﻿using System;
using System.Linq;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlServerCe;
using PolePositionMobileUtilityGeneral;
using System.Data;

namespace PolePositionMobileUtilityData
{
    public class DNote
    {
        public static DataTable GetByInspectionResult(int inspectionResultId)
        {
            DataTable _Dt = new DataTable();
            SqlCeConnection _Con = null;
            SqlCeDataAdapter _Da = null;
            StringBuilder _sql = new StringBuilder();
            try
            {
                _Con = Utils.GetConnection();
                #region Query
                _sql.AppendLine("Select ");
                _sql.AppendLine("   I.NoteFk, ");
                _sql.AppendLine("   N.Name as Note_Name ");
                _sql.AppendLine("FROM tb_UtilityInspectionResultsXNotes I ");
                _sql.AppendLine("   INNER JOIN tb_Notes N ON I.NoteFk = N.NoteId ");
                _sql.AppendLine("Where UtilityInspectionResultFk = @UtilityInspectionResultFk");
                #endregion Query
                _Da = new SqlCeDataAdapter(_sql.ToString(), _Con);
                _Da.SelectCommand.CommandType = CommandType.Text;
                _Da.SelectCommand.Parameters.Add(Utils.CreateIntParameter("@UtilityInspectionResultFk", ParameterDirection.Input, false, inspectionResultId));
                _Da.Fill(_Dt);
                return _Dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (_Con != null)
                {
                    _Con.Close();
                }
            }
        }

        public static void DeleteNoteByInspectionResult(int inspectionResultId, SqlCeTransaction tr)
        {
            StringBuilder _Sql = new StringBuilder(); ;
            SqlCeCommand _Cmd;
            #region Query
            _Sql.AppendLine("DELETE tb_UtilityInspectionResultsXNotes ");
            _Sql.AppendLine("Where UtilityInspectionResultFk = @UtilityInspectionResultFk");
            #endregion Query
            _Cmd = new SqlCeCommand(_Sql.ToString(), (SqlCeConnection)tr.Connection);
            _Cmd.Transaction = tr;
            #region Parameters
            _Cmd.Parameters.Add(Utils.CreateIntParameter("@UtilityInspectionResultFk", ParameterDirection.Input, false, inspectionResultId));
            #endregion Parameters
            _Cmd.ExecuteNonQuery();
        }
        
        public static void Insert(int noteId,int resultId, SqlCeTransaction tr)
        {
            StringBuilder _Sql = new StringBuilder(); ;
            SqlCeCommand _Cmd;
            #region Query
            _Sql.AppendLine("Insert into tb_UtilityInspectionResultsXNotes (UtilityInspectionResultFk,NoteFk) ");
            _Sql.AppendLine("Values(@UtilityInspectionResultFk,@NoteFk)");
            #endregion Query
            _Cmd = new SqlCeCommand(_Sql.ToString(), (SqlCeConnection)tr.Connection);
            _Cmd.Transaction = tr;
            #region Parameters
            _Cmd.Parameters.Add(Utils.CreateIntParameter("@UtilityInspectionResultFk", ParameterDirection.Input, false, resultId));
            _Cmd.Parameters.Add(Utils.CreateIntParameter("@NoteFk", ParameterDirection.Input, false, noteId));
            #endregion Parameters
            _Cmd.ExecuteNonQuery();
        }
    }
}
